/*
 *    GeoTools - The Open Source Java GIS Toolkit
 *    http://geotools.org
 *
 *    (C) 2002-2008, Open Source Geospatial Foundation (OSGeo)
 *
 *    This library is free software; you can redistribute it and/or
 *    modify it under the terms of the GNU Lesser General Public
 *    License as published by the Free Software Foundation;
 *    version 2.1 of the License.
 *
 *    This library is distributed in the hope that it will be useful,
 *    but WITHOUT ANY WARRANTY; without even the implied warranty of
 *    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 *    Lesser General Public License for more details.
 */
package org.geotools.geopkg;

import org.geotools.geometry.jts.GeometryBuilder;
import org.geotools.jdbc.JDBCPrimaryKeyTestSetup;

public class GeoPkgPrimaryKeyTestSetup extends JDBCPrimaryKeyTestSetup {

    protected GeoPkgPrimaryKeyTestSetup() {
        super(new GeoPkgTestSetup());
    }

    @Override
    protected void createAutoGeneratedPrimaryKeyTable() throws Exception {
        run("CREATE TABLE auto( fid INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR,geom BLOB)");
        // run("SELECT AddGeometryColumn('auto','geom',4326,'POINT',2)");
        String sql =
                "INSERT INTO gpkg_geometry_columns VALUES ('auto', 'geom', 'POINT', 4326, 0, 0)";
        run(sql);
        sql =
                "INSERT INTO gpkg_contents (table_name, data_type, identifier, srs_id) VALUES "
                        + "('auto', 'features', 'auto', 4326)";
        run(sql);
        GeometryBuilder gb = new GeometryBuilder();
        GeoPkgTestSetup setup = (GeoPkgTestSetup) delegate;

        run("INSERT INTO auto VALUES (1, 'one', X'" + setup.toString(gb.point(1, 1)) + "')");
        run("INSERT INTO auto VALUES (2, 'two', X'" + setup.toString(gb.point(2, 2)) + "')");
        run("INSERT INTO auto VALUES (3, 'three', X'" + setup.toString(gb.point(4, 4)) + "')");
    }

    @Override
    protected void dropAutoGeneratedPrimaryKeyTable() throws Exception {
        ((GeoPkgTestSetup) delegate).removeTable("auto");
    }

    @Override
    protected void createMultiColumnPrimaryKeyTable() throws Exception {
        run(
                "CREATE TABLE multi( key1 INTEGER NOT NULL, key2 VARCHAR NOT NULL, name VARCHAR, PRIMARY KEY(key1,key2))");

        run("ALTER TABLE multi add geom BLOB");
        String sql =
                "INSERT INTO gpkg_geometry_columns VALUES ('multi', 'geom', 'POINT', 4326, 0, 0)";
        run(sql);
        sql =
                "INSERT INTO gpkg_contents (table_name, data_type, identifier, srs_id) VALUES "
                        + "('multi', 'features', 'multi', 4326)";
        run(sql);
        GeometryBuilder gb = new GeometryBuilder();
        GeoPkgTestSetup setup = (GeoPkgTestSetup) delegate;

        run("INSERT INTO multi VALUES (1, 'x', 'one', X'" + setup.toString(gb.point(1, 1)) + "')");
        run("INSERT INTO multi VALUES (2, 'y', 'two', X'" + setup.toString(gb.point(2, 2)) + "')");
        run(
                "INSERT INTO multi VALUES (3, 'z', 'three', X'"
                        + setup.toString(gb.point(4, 4))
                        + "')");
    }

    @Override
    protected void dropMultiColumnPrimaryKeyTable() throws Exception {
        ((GeoPkgTestSetup) delegate).removeTable("multi");
    }

    @Override
    protected void createNonIncrementingPrimaryKeyTable() throws Exception {
        run("CREATE TABLE noninc ( key int PRIMARY KEY, name VARCHAR, geom BLOB)");
        // run("SELECT AddGeometryColumn('noninc','geom',4326,'POINT',2)");
        String sql =
                "INSERT INTO gpkg_geometry_columns VALUES ('noninc', 'geom', 'POINT', 4326, 0, 0)";
        run(sql);
        sql =
                "INSERT INTO gpkg_contents (table_name, data_type, identifier, srs_id) VALUES "
                        + "('noninc', 'features', 'noninc', 4326)";
        run(sql);
        run("INSERT INTO noninc VALUES (1, 'one', NULL)");
        run("INSERT INTO noninc VALUES (2, 'two', NULL)");
        run("INSERT INTO noninc VALUES (3, 'three', NULL)");
    }

    @Override
    protected void dropNonIncrementingPrimaryKeyTable() throws Exception {
        ((GeoPkgTestSetup) delegate).removeTable("noninc");
    }

    @Override
    protected void createSequencedPrimaryKeyTable() throws Exception {}

    @Override
    protected void createNonFirstColumnPrimaryKey() throws Exception {
        run(
                "CREATE TABLE nonfirst(name VARCHAR, fid INTEGER PRIMARY KEY AUTOINCREMENT, geom BLOB)");
        // run("SELECT AddGeometryColumn('nonfirst','geom',4326,'POINT',2)");
        String sql =
                "INSERT INTO gpkg_geometry_columns VALUES ('nonfirst', 'geom', 'POINT', 4326, 0, 0)";
        run(sql);
        sql =
                "INSERT INTO gpkg_contents (table_name, data_type, identifier, srs_id) VALUES "
                        + "('nonfirst', 'features', 'nonfirst', 4326)";
        run(sql);
        GeometryBuilder gb = new GeometryBuilder();
        GeoPkgTestSetup setup = (GeoPkgTestSetup) delegate;

        run("INSERT INTO nonfirst VALUES ('one',1, X'" + setup.toString(gb.point(1, 1)) + "')");
        run("INSERT INTO nonfirst VALUES ('two',2, X'" + setup.toString(gb.point(2, 2)) + "')");
        run("INSERT INTO nonfirst VALUES ('three',3, X'" + setup.toString(gb.point(4, 4)) + "')");
    }

    @Override
    protected void dropSequencedPrimaryKeyTable() throws Exception {}

    @Override
    protected void createNullPrimaryKeyTable() throws Exception {
        run("CREATE TABLE nokey (name VARCHAR)");
        String sql =
                "INSERT INTO gpkg_contents (table_name, data_type, identifier, srs_id) VALUES "
                        + "('nokey', 'features', 'nokey', 4326)";
        run(sql);
        run("INSERT INTO nokey VALUES ('one')");
        run("INSERT INTO nokey VALUES ('two')");
        run("INSERT INTO nokey VALUES ('three')");
    }

    @Override
    protected void dropNullPrimaryKeyTable() throws Exception {
        ((GeoPkgTestSetup) delegate).removeTable("nokey");
    }

    @Override
    protected void createUniqueIndexTable() throws Exception {

        run("CREATE TABLE uniq ( key int, name VARCHAR, geom BLOB)");
        // run( "SELECT AddGeometryColumn('uniq','geom',4326,'POINT',2)");
        String sql =
                "INSERT INTO gpkg_geometry_columns VALUES ('uniq', 'geom', 'POINT', 4326, 0, 0)";
        run(sql);
        run("CREATE UNIQUE INDEX uniq_key_index ON uniq ( key )");
        sql =
                "INSERT INTO gpkg_contents (table_name, data_type, identifier, srs_id) VALUES "
                        + "('uniq', 'features', 'uniq', 4326)";
        run(sql);
        GeometryBuilder gb = new GeometryBuilder();
        GeoPkgTestSetup setup = (GeoPkgTestSetup) delegate;

        run("INSERT INTO uniq VALUES (1, 'one', X'" + setup.toString(gb.point(1, 1)) + "')");
        run("INSERT INTO uniq VALUES (2, 'two', X'" + setup.toString(gb.point(2, 2)) + "')");
        run("INSERT INTO uniq VALUES (3, 'three', X'" + setup.toString(gb.point(4, 4)) + "')");
    }

    @Override
    protected void dropUniqueIndexTable() throws Exception {
        ((GeoPkgTestSetup) delegate).removeTable("uniq");
    }

    @Override
    protected void dropNonFirstPrimaryKeyTable() throws Exception {
        ((GeoPkgTestSetup) delegate).removeTable("nonfirst");
    }
}
